{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database version : 5.7.22 \n"
     ]
    }
   ],
   "source": [
    "import MySQLdb\n",
    "\n",
    "db = MySQLdb.connect('localhost','root','root')\n",
    "cursor = db.cursor()\n",
    "\n",
    "cursor.execute('select version()')\n",
    "data = cursor.fetchone()\n",
    "print 'Database version : %s ' % data\n",
    "\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import MySQLdb\n",
    "\n",
    "db = MySQLdb.connect('localhost','root','root','test',charset='utf8')\n",
    "cursor = db.cursor()\n",
    "\n",
    "cursor.execute(\"DROP TABLE IF EXISTS EMPLOYEE\")\n",
    "\n",
    "sql = \"\"\"CREATE TABLE EMPLOYEE (\n",
    "         first_name  CHAR(20) NOT NULL,\n",
    "         last_name  CHAR(20),\n",
    "         age INT,  \n",
    "         sex CHAR(1),\n",
    "         income FLOAT )\"\"\"\n",
    "\n",
    "data = cursor.execute(sql)\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import MySQLdb\n",
    "\n",
    "\n",
    "# 插入操作\n",
    "db = MySQLdb.connect('localhost','root','root','test',charset='utf8')\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql = \"\"\"insert into EMPLOYEE (\n",
    "         first_name,\n",
    "         last_name,\n",
    "         age,  \n",
    "         sex,\n",
    "         income) values('Lulin','Dong',29,'M',18000)\"\"\"\n",
    "\n",
    "try:\n",
    "    cursor.execute(sql)\n",
    "    db.commit()\n",
    "except:\n",
    "    db.rollback()\n",
    "    \n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "insert into EMPLOYEE (\n",
      "         first_name,\n",
      "         last_name,\n",
      "         age,  \n",
      "         sex,\n",
      "         income) values('Lulin','Dong',29,'M',18000)\n"
     ]
    }
   ],
   "source": [
    "import MySQLdb\n",
    "\n",
    "\n",
    "# 插入操作\n",
    "db = MySQLdb.connect('localhost','root','root','test',charset='utf8')\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql_insert = \"\"\"insert into employee(first_name,last_name,age,sex,income) values(%s ,%s ,%s ,%s ,%s)\"\"\"\n",
    "\n",
    "print sql\n",
    "try:\n",
    "    cursor.executemany(sql_insert,[(\"陈\", \"二狗\", 50,\"M\", 2000), (\"吴\", \"三毛\", 47,\"F\", 2500), (\"李\", \"狗蛋\",43, \"M\", 3500)])\n",
    "    db.commit()\n",
    "except Exception as e:\n",
    "    print e.message\n",
    "    db.rollback()\n",
    "    \n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select * from employee                 where income > 1000\n",
      "fname=Lulin,lname=Dong,age=29,sex=M,income=18000.0\n",
      "fname=陈,lname=二狗,age=50,sex=M,income=2000.0\n",
      "fname=吴,lname=三毛,age=47,sex=F,income=2500.0\n",
      "fname=李,lname=狗蛋,age=43,sex=M,income=3500.0\n"
     ]
    }
   ],
   "source": [
    "import MySQLdb\n",
    "\n",
    "\n",
    "# 插入操作\n",
    "db = MySQLdb.connect('localhost','root','root','test',charset='utf8')\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql_query = \"select * from employee \\\n",
    "                where income > %s\" % (1000)\n",
    "\n",
    "print sql_query\n",
    "try:\n",
    "    cursor.execute(sql_query)\n",
    "    \n",
    "    results = cursor.fetchall()\n",
    "    \n",
    "    for row in results:\n",
    "        fname = row[0]\n",
    "        lname = row[1]\n",
    "        age = row[2]\n",
    "        sex = row[3]\n",
    "        income = row[4]\n",
    "    \n",
    "        print \"fname=%s,lname=%s,age=%s,sex=%s,income=%s\" % \\\n",
    "             (fname, lname, age, sex, income )\n",
    "    \n",
    "except Exception as e:\n",
    "    print \"Error: unable to fecth data\"\n",
    "    print e.message\n",
    "    \n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "update employee set age = age + 1 where sex = 'M'\n"
     ]
    }
   ],
   "source": [
    "import MySQLdb\n",
    "\n",
    "\n",
    "# 插入操作\n",
    "db = MySQLdb.connect('localhost','root','root','test',charset='utf8')\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql_update = \"update employee set age = age + 1 where sex = '%c'\" % ('M')\n",
    "\n",
    "print sql_update\n",
    "try:\n",
    "    cursor.execute(sql_update)\n",
    "    \n",
    "    db.commit()\n",
    "    \n",
    "except:\n",
    "    print \"Error: unable to update data\"\n",
    "    \n",
    "    \n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "delete from employee where age > '40'\n"
     ]
    }
   ],
   "source": [
    "import MySQLdb\n",
    "\n",
    "\n",
    "# 插入操作\n",
    "db = MySQLdb.connect('localhost','root','root','test',charset='utf8')\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql_delete = \"delete from employee where age > '%s'\" % (40)\n",
    "\n",
    "print sql_delete\n",
    "try:\n",
    "    cursor.execute(sql_delete)\n",
    "    \n",
    "    db.commit()\n",
    "    \n",
    "except :\n",
    "    print \"Error: unable to delete data\"\n",
    "    \n",
    "    \n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
